package com.glsc.ngateway.common.base.repo.mysql.gateway.platform;

import com.glsc.ngateway.common.base.domain.mysql.gateway.platform.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;
import java.util.Set;

@Repository
public interface MysqlUserRepository extends JpaRepository<User, Long>, JpaSpecificationExecutor<User> {

    User findByUserid(String userId);

    //@Lock(value = LockModeType.PESSIMISTIC_WRITE)
    @Query(value = "select u from User u where u.id = :id")
    User queryByIdLock(@Param("id") long id);

    @Transactional
    @Modifying
    @Query(value = "update User u set u.deleted = 1 where u.id = :id")
    int deleteById(@Param("id") long id);

    @Transactional(readOnly = true)
    @Query(value = "select u from User u where u.username = :username and u.deleted = 0")
    User findByUsername(@Param("username") String username);

    @Transactional(readOnly = true)
    @Query(value = "select u from User u where u.id = :id and u.deleted = 0")
    User findById(@Param("id") long id);

    @Transactional(readOnly = true)
    @Query(value = "select u from User u where u.id = :id ")
    User findByIdAll(@Param("id") long id);


    @Query(value = "select u.url from t_user_role ur join t_role_url ru on ur.role_id=ru.role_id join t_url u on " +
            "ru.url_id=u.id and ur.user_id=:id and u.b_is_del = 0", nativeQuery = true)
    Set<String> queryUrlsByUserId(@Param("id") long id);

    @Transactional
    @Modifying
    @Query(value = "update User u set u.password = :pwd where u.id = :id")
    void updatePassword(@Param("id") long id, @Param("pwd") String pwd);

    @Query(value = "select * from t_user u join t_user_role r on u.id=r.user_id where r.role_id = :roleId", nativeQuery = true)
    List<User> findByRoleId(@Param("roleId") long roleId);

    @Query(value = "select * from t_user u join t_user_report_subscribe r on u.id=r.user_id where r.report_id = :reportId", nativeQuery = true)
    List<User> findByReportId(@Param("reportId") String reportId);

    @Query(value = "select a.user_id from t_it_network_device a left join t_user u on a.user_id = u.userid where u.userid is null\n" +
            "UNION \n" +
            "select a.user_id from t_it_server_device a left join t_user u on a.user_id = u.userid where u.userid is null\n" +
            "UNION \n" +
            "select a.user_id from t_it_virtual_device a left join t_user u on a.user_id = u.userid where u.userid is null\n" +
            "UNION \n" +
            "select a.user_id from t_it_cloud_device a left join t_user u on a.user_id = u.userid where u.userid is null\n", nativeQuery = true)
    List<String> getUnLoginUser();

    List<User> findAllByUsernameIn(List<String> userName);

    @Query(value = "select * from t_user u join t_user_role r on u.id=r.user_id where r.role_id in :roleIds and u.b_is_del = 0", nativeQuery = true)
    List<User> findAllByRoleIdIn(@Param("roleIds") List<Long> roleIds);

    @Query(value = "select * from t_user u join t_user_role r on u.id=r.user_id where u.department in :depList and u.b_is_del = 0", nativeQuery = true)
    List<User> findAllByUserDepartmentIn(@Param("depList") List<String> depList);
}
